/*
 * Connector.java
 *
 * Created on December 15, 2007, 4:16 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.fie.database;


import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author joseacuna-rohter
 */
public class Connector {
    
    private Connection con = null;
    public static Connector myConnector = new Connector();
    private List watchList;
    
    /** Creates a new instance of Connector */
    private Connector() {        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://tusapuestas.com.pe:3306/rohter_fie","rohter_x", "x");
            System.out.println("got connection");
        }catch(Exception e){
            e.printStackTrace();
        }   
    }
    
    
    
    /*
     *Login Script
     *
     *
     */
    public boolean logIn(String username,char[] p){
        
        String password = "";
        for(int i = 0; i<p.length; i++)
           password = password + p[i];
        
        try{
            byte[] digest;
            MessageDigest md = MessageDigest.getInstance("SHA");
            md.update(password.getBytes("UTF-8"));
            
            digest = md.digest();
            
            StringBuffer hexString = new StringBuffer();
            for (int i=0;i<digest.length;i++) {
		hexString.append(Integer.toHexString(0xFF & digest[i]));
            }
       
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customers" +
                    " WHERE username ='"+username+"' " +
                    "AND password = SHA1('"+password+"')");
            
            System.out.println("Finished Log In");
            if(rs.next()){
                return true;
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }       
        return false;
    }
    
    
    /*
     *
     *CREATE NEW USER
     *
     */
    
    
    
    
    /*
     *
     *Get a list of stocks for a certain user
     *
     */
    public List getWatchList(int customerId){
        
        watchList = new ArrayList();
        
        try{
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM watchlist" +
                    " WHERE customer_id ="+customerId);
            
            while(rs.next()){
                watchList.add(rs.getString(2));                
            }
            System.out.println("Finished getting watch List");
        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        return watchList;        
    }
    
    
    /*
     *
     *add an item to the watchList of a customer
     *
     */
    public void addToWatchList(int customerId,String ticker){     
        try{
            Statement st = con.createStatement();
            st.execute("INSERT INTO watchlist(customer_id,stock)" 
               + " VALUES ("+customerId+",'"+ticker+"')");          
        }
        catch(Exception e){
            e.printStackTrace();
        }          
    }
    
    
    /*
     *
     *Remove an item to the watchList of a customer
     *
     */
    public void removeFromWatchList(int customerId,String ticker){     
        try{
            Statement st = con.createStatement();
            st.execute("DELETE FROM watchlist WHERE" 
               + " customer_id = "+customerId+" AND stock = '"+ticker+"'");          
        }
        catch(Exception e){
            e.printStackTrace();
        }          
    }
    
    
    
    
    /***
     *
     *Get customer_id by supplying username
     *
     ***/
    public int getCustomerID(String username){
        
        try{
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM customers" +
                    " WHERE username ='"+username+"'");
        
        rs.next();
        return Integer.parseInt(rs.getString(1));
        
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return -1;
    }
    
    public float getCustomerBalance(int customerId){
        
        try{
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customers" +
                        " WHERE customer_id ="+customerId+"");

            rs.next();
            return rs.getFloat(4);

        }
        catch(Exception e){
            e.printStackTrace();
        }
        return -1;
    }
    
    
    public void purchaseStock(int customerId,String ticker,
            int amount,float price,String time,float totalAmount){
        try{
            Statement st = con.createStatement();
            st.execute("INSERT INTO customerpositions" +
                    " VALUES ( "+customerId+",'"+ticker+"',"+amount+","+price+",'"+time+"'" +
                    ",'"+totalAmount+"','a')");          
        }
        catch(Exception e){
            e.printStackTrace();
        }          
        
    }
    
    
    /**
     * This is new and improved purchaseStock method
     *
     **/
    public void purchaseStockTwo(int customerId,String ticker,
            int amount, float price, String time, float totalAmount){
        
        String queryString = "SELECT * FROM positionstracker " +
                "WHERE ticker = '"+ticker+"' AND customer_id = " +customerId ;
        
       try{
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(queryString);
            
            if(rs.next()){
                System.out.println("IT EXISTS");
                st.executeUpdate("UPDATE positionstracker SET amount = amount + "
                        +amount+",cashamount = cashamount + "+totalAmount+" WHERE  customer_id = " +customerId + 
                        " AND ticker = '"+ticker+"'");
            }
            else{
                System.out.println("IT DOES NOT EXIST; LETS CREATE");
                st.execute("INSERT INTO positionstracker " +
                        "VALUES(" + customerId + ",'" + ticker + "',"
                        + amount + "," + totalAmount+")");
            }
            
            
        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        
    }
    
    public ResultSet getActivePositionsTwo(int customerId){
         try{
             System.out.println("CUSTOMER ID  = " + customerId);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM positionstracker" +
                    " WHERE customer_id="+customerId+"");
            return rs;

        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        return null;
    }
    
    
    public void updateBalance(int customerId,float totalPrice){
         try{
            Statement st = con.createStatement();
            st.execute("UPDATE customers SET balance = balance - "+ totalPrice + " WHERE" +
                    " customer_id = " + customerId);          
        }
        catch(Exception e){
            e.printStackTrace();
        }          
    }
    
    public ResultSet getActivePositions(int customerId){
         try{
             System.out.println("CUSTOMER ID  = " + customerId);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customerpositions" +
                    " WHERE customer_id="+customerId+" AND status = 'a'" +
                    " AND amount <> 0");
            return rs;

        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        return null;
    }
    
    
    
   public ResultSet getActivePosition(int customerId,String ticker){
         try{
             System.out.println("CUSTOMER ID  = " + customerId);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM customerpositions" +
                    " WHERE customer_id="+customerId+" AND status = 'a'" +
                    " AND stock = '"+ticker+"' AND amount <> 0");
            return rs;

        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        return null;
    }
   
   
   
    public ResultSet getActivePositionTwo(int customerId,String ticker){
         try{
            System.out.println("CUSTOMER ID  = " + customerId);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM positionstracker" +
                    " WHERE customer_id="+customerId+
                    " AND ticker = '"+ticker+"' AND amount <> 0");
            return rs;

        }
        catch(Exception e){
            e.printStackTrace();
        }
        
        return null;
    }
   
    
   
   public void sellStock(int customerId,String ticker, int amount,float cashamount){
        try{
            String sql = "UPDATE positionstracker SET " +
                    "amount = amount - "+amount+
                    " ,cashamount = cashamount - " + cashamount +
                    " WHERE ticker ='"+ticker+"'" +
                    " AND customer_id = " + customerId ;
            Statement st = con.createStatement();
            System.out.println(sql);
            st.execute(sql);          
        }
        catch(Exception e){
            e.printStackTrace();
        } 
       
   }
   
   
   public synchronized void cleanDatabase(){
       ResultSet rs = null;
        try{            
            Statement st = con.createStatement();                      
            String sql = "DELETE FROM positionstracker WHERE amount = 0";
            
            System.out.println(sql);
            st.execute(sql);          
        }
        catch(Exception e){
            e.printStackTrace();
        }
       
   }
   
   
public ResultSet getClosedPositions(){
       ResultSet rs = null;
        try{            
            Statement st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM positionstracker" +
                    " WHERE amount = 0");
            return rs;
           
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return rs;
}
 

public int getAmount(String ticker,int customerId){
     ResultSet rs = null;
        try{            
            Statement st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM positionstracker" +
                    " WHERE ticker = '"+ticker+"' AND customer_id="+customerId);
            rs.next();
            return rs.getInt(3);
           
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return -1;  
}

public float getCashAmount(String ticker,int customerId){
     ResultSet rs = null;
        try{            
            Statement st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM positionstracker" +
                    " WHERE ticker = '"+ticker+"' AND customer_id="+customerId);
            rs.next();
            return rs.getFloat(4);
           
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return -1;  
}


   
    
    
    public Connector getConnection(){
        return myConnector;
    }
    
    
    
}
